
import java.io.File;
import java.io.FileInputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class DBComm {

    private Connection conn;
    private Statement stmnt;
    Robot robot;

    public DBComm() {
    }

    public DBComm(Robot robot) {
        this.robot = robot;
    }

    protected void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://aasudrl1.db.6601164.hostedresource.com/aasudrl1", "aasudrl1", "Csci1301");
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            if (!conn.getMetaData().getTables(null, null, "host_to_applet", null).next()) {
                stmnt.execute("create table host_to_applet (robot_id VARCHAR(8), heading VARCHAR(1), x INTEGER, y INTEGER, leftLine INTEGER, fwdLine INTEGER, rightLine INTEGER)");
            }
            if (!conn.getMetaData().getTables(null, null, "applet_to_host", null).next()) {
                stmnt.execute("create table applet_to_host (robot_id VARCHAR(8), heading VARCHAR(1))");
            }
            if (!conn.getMetaData().getTables(null, null, "queue", null).next()) {
                stmnt.execute("create table queue (id VARCHAR(11), position INTEGER, timestamp TIMESTAMP)");
            }
            if (!conn.getMetaData().getTables(null, null, "video", null).next()) {
                stmnt.execute("create table video (name VARCHAR(10), frame BLOB)");
                stmnt.execute("insert into video(name, frame) values('frame',NULL)");
            }
            ResultSet rs = stmnt.executeQuery("select * from video;");
            if (!rs.next()) {
                stmnt.execute("insert into video(name, frame) values('frame',NULL)");
            } else {
                System.out.println("Theres a video frame in the db");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected byte[] read() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            ResultSet rs = stmnt.executeQuery("select heading from applet_to_host where robot_id='" + robot.robotID + "';");
            if (rs.next()) {
                byte dir = (byte) (rs.getString(1).charAt(0));
                stmnt.execute("delete from applet_to_host;");
                rs.close();
                System.out.println("Robot: " + robot.robotID + "   Direction: " + dir);
                return new byte[]{1, dir};
            } else {
                try {
                    Thread.sleep(3000);
                } catch (Exception ex) {
                }
                rs.close();
                return null;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }

    }

    protected void write() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            stmnt.execute("insert into host_to_applet values ('" + robot.robotID + "', '" + robot.heading + "', '" + robot.point.x + "', '" + robot.point.y + "', '" + robot.leftPath + "', '" + robot.fwdPath + "', '" + robot.rightPath + "');");
            System.out.println("Executed insert");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    protected void clear() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            stmnt.execute("delete from applet_to_host;");
            stmnt.execute("delete from host_to_applet;");
            stmnt.execute("delete from queue;");
            stmnt.execute("delete from video;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    protected void updateQueueSummary() {
        /*try {
            ResultSet rs = stmnt.executeQuery("select * from queue;");
            ResultSetMetaData r = rs.getMetaData();
            for (int i = 0; i < r.getColumnCount(); i++) {
                System.out.print(r.getColumnName(i));
            }
            System.out.println("");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        //Host.queueSummary*/

    }

    protected void disconnect() {
        clear();
        try {
            if (!conn.isClosed()) {
                stmnt.close();
                conn.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected void uploadFrame(File image) {
        try {
            if (image.length() > 0) {
                FileInputStream fis = new FileInputStream(image);
                PreparedStatement pstmt = conn.prepareStatement("update video set frame=? where name='frame'");
                pstmt.setBinaryStream(1, fis, (int) image.length());
                pstmt.executeUpdate();
                pstmt.close();
                fis.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected void checkStamp() {

        java.sql.Timestamp currentTime = null;
        java.sql.Timestamp clientTime;
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            ResultSet time = stmnt.executeQuery("select now();");
            if (time.next()) {
                currentTime = time.getTimestamp(1);
            }
            time.close();
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            ResultSet rs = stmnt.executeQuery("Select id, position from queue where position = 0;");
            while (rs.next()) {
                String id = rs.getString(1);
                ResultSet rsMax = stmnt.executeQuery("Select max(position) from queue;");
                rsMax.next();
                int max = rsMax.getInt(1);
                stmnt.executeUpdate("update queue set position= " + (max + 1) + " where id = '" + id + "';");
            }

            rs.close();

            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            ResultSet rs2 = stmnt.executeQuery("select * from queue;");

            while (rs2.next()) {
                clientTime = rs2.getTimestamp(3);
                int position = rs2.getInt(2);
                String id = rs2.getString(1);
                if (Math.abs(currentTime.getTime() - clientTime.getTime()) >= 10000) {
                    stmnt.execute("delete from queue where id='" + id + "';"); //Delete the client that has timed out
                    //Repositions all clients behind the client that got deleted.
                    rs2 = stmnt.executeQuery("select * from queue;"); // Need to refresh result. DON'T DELETE
                    if (rs2 == null) {
                        break;
                    }
                    stmnt.executeUpdate("update queue set position=position-1 where position > " + position + ";");
                }
            }
            rs2.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
